from datetime import datetime, timedelta

import pandas as pd


def read_excel():
    """
    读取excel方法
    :return:
    """
    excel_file = "data.xlsx"
    excel = pd.read_excel(excel_file, sheet_name="工作表1")
    excel = excel.fillna(0)
    return excel


def calculate_money(data):
    """
    生成用户的工资条
    :param data:
    :return: dict
    """
    end_time = datetime(year=1997, month=4, day=30)
    name = data["姓名"]
    in_date = data["入职时间"]
    salary = float(data["当前月薪"])
    due_days = data["应出勤天数"]
    actual_days = int(data["实际出勤天数"])
    annual_vacation = int(data["年假天数"])
    sick_vacation = int(data["病假天数"])
    actual_salary = data["应发税前工资"]
    if in_date < datetime(year=1997, month=4, day=1):
        in_date_num = calculate_due_days(datetime(year=1997, month=4, day=1), end_time)
    else:
        in_date_num = calculate_due_days(in_date, end_time)
    # 事假天数 = 应出勤天数-实际出勤天数-年假天数-病假天数
    thing_vacation = in_date_num - actual_days - annual_vacation - sick_vacation
    if thing_vacation < 0:
        thing_vacation = 0
    # 计算四月工作日天数
    work_days = calculate_due_days(datetime(year=1997, month=4, day=1), end_time)
    # 计算日工资
    day_salary = salary / work_days
    # 计算实际计薪天数 = 实际出勤工资 + 病假工资
    work_salary = day_salary * actual_days
    sick_salary = sick_vacation * day_salary * 0.5
    actual_salary = round(work_salary + sick_salary, 2)
    data["入职时间"] = in_date.strftime('%Y-%m-%d')
    data["应出勤天数"] = in_date_num
    data["事假天数"] = thing_vacation
    data["应发税前工资"] = actual_salary
    return data


def calculate_due_days(beg, end):
    """
    计算两个日期之间的工作日
    :param beg:
    :param end:
    :return:
    """
    num = end - beg  # 计算两个日期之间的差
    due_days = 0  # 应出勤天数
    if num.days >= 0:
        for i in range(num.days + 1):
            temp = beg + timedelta(days=i)
            # 判断是否为周末
            if temp.isoweekday() not in [6, 7]:
                due_days += 1
    else:
        due_days = -1
    return due_days


if __name__ == "__main__":
    excel_data = read_excel()
    new_data = list()
    for index, row in excel_data.iterrows():
        new_data.append(calculate_money(row).values)
    new_df = pd.DataFrame(new_data, columns=['姓名', '入职时间', '当前月薪', '应出勤天数', '实际出勤天数', '年假天数', '病假天数', '应发税前工资', '事假天数'])
    new_df.to_excel("salary.xlsx", sheet_name="工资条", index=False)
